package com.zhf.testDBUtils;

import com.zhf.entity.Employee;
import com.zhf.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @Author: ZhaoHuanfeng
 * @Date: 2020/9/24 22:50
 */


public class DBUtilsDemo03 {
    /**
     * 查询id为5的记录,封装到数组中
     * ArrayHandler 将结果集的第一条数据封装到数组中
     */
    @Test
    public void testFindById() throws SQLException {
        //创建QueryRunner对象
        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());

        //编写SQL
        String sql="select * from employee where eid=?";

        //执行SQL
        Object[] query = queryRunner.query(sql, new ArrayHandler(), 5);

        //获取数据
        System.out.println(Arrays.toString(query));
    }

    /**
     * 查询所有数据，封装到List集合中
     *  ArrayListHandler可以将每条数据线封装到数组中，再将数组封装到集合中
     */
    @Test
    public void testFindAll() throws SQLException {
        //创建QueryRunner
        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());

        //编写SQL
        String sql="select * from employee;";

        //执行SQL
        List<Object[]> query = queryRunner.query(sql, new ArrayListHandler());

        //遍历list获取数据
        for (Object[] objects:query){
            System.out.println(Arrays.toString(objects));
        }
    }

    /**
     * 查询id为5的记录，封装到指定JavaBean中
     * BeanHandler将结果集的第一条数据封装到JavaBean中
     */
    @Test
    public void testFindByIdJavaBean() throws SQLException {
        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());

        String sql="select * from employee where eid=?";

        Employee query = queryRunner.query(sql, new BeanHandler<Employee>(Employee.class), 5);

        System.out.println(query);

    }

    /**
     * 查询薪资大于3000的所有员工信息，封装到JavaBean中再封装到集合中
     * BeanListHandler 将结果集的每一条数据封装到JavaBean中，再将JavaBean放到集合中
     */
    @Test
    public void testFindBySalary() throws SQLException {
        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());

        String sql="select * from employee where salary>3000";

        List<Employee> query = queryRunner.query(sql, new BeanListHandler<Employee>(Employee.class));

        for (Employee employee:query){
            System.out.println(employee);
        }
    }

    /**
     * 查询姓名是张三的员工信息，将结果封装到Map集合中
     * MapHandler 将结果集的第一条记录封装到Map<String,Object>中
     * key对应 列名  value对应 列的值
     */
    @Test
    public void testFindByName() throws SQLException {
        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());

        String sql="select * from employee where ename='张三'";

        Map<String, Object> query = queryRunner.query(sql, new MapHandler());

        Set<Map.Entry<String, Object>> set = query.entrySet();
        for (Map.Entry<String,Object> result : set){
            System.out.println(result.getKey()+" = "+result.getValue());
        }
    }

    /**
     * 查询所有员工的薪资总额
     * ScalarHandler 用于封装单个数据
     */
    @Test
    public void testSumSalary() throws SQLException {
        QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());

        String sql="select sum(salary) from employee";

        Object query = queryRunner.query(sql, new ScalarHandler<>());

        System.out.println(query);
    }

}
